/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package SQLManager;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class SQLManager extends SQLiteOpenHelper{

	protected static Context context;
	
	private static SQLManager mSQLManager = null;
	//-------------------------------
	//	Constants
	//-------------------------------
	
	//--------Event Table Key
	public static final String TB_EVENTTABLE = "EventTable";
	public static final String TB_LOCAL_CONTACTLIST_TABLE = "LocalContactListTable";
	public static final String TB_UPDATETABLE = "UpdateTable";
	public static final String TB_PRODUCT_TABLE = "ProductList_%s";
	public static final String TB_TEMP_PRODUCT_TABLE = "ProductList_T%s";
	public static final String TB_DEBT_TABLE = "DebtTable_%s";
	
	// -------KEY Constant
	
	// Event Table
	public static final String KEY_EVTTB_ID = "ID";
	public static final String KEY_EVTTB_EVENTID = "EventID";
	public static final String KEY_EVTTB_MASTER = "Master";
	public static final String KEY_EVTTB_TITLE = "Title";
	public static final String KEY_EVTTB_CONTACTMEMBER = "ContactMemeber";
	public static final String KEY_EVTTB_LOCATION = "Location";
	public static final String KEY_EVTTB_ISTERMINATED = "IsTerminated";

	// Contact List
	public static final String KEY_LCLTB_USERID = "UserID";
	public static final String KEY_LCLTB_USERNAME = "UserName";
	public static final String KEY_LCLTB_PHONENUMBER = "PhoneNumber";
	
	
	// Product List
	public static final String KEY_PLTB_ID = "ID";
	public static final String KEY_PLTB_PRODUCT_ID = "ProductID";
	public static final String KEY_PLTB_OWNER = "Owner";
	public static final String KEY_PLTB_PRODUCT_NAME = "ProductName";
	public static final String KEY_PLTB_DESCRIPTION = "Description";
	public static final String KEY_PLTB_PRICE = "Price";
	public static final String KEY_PLTB_QUALITY = "Quality";
	public static final String KEY_PLTB_IMAGE = "Image";
	
	// Update Table
	public static final String KEY_UPTB_ID = "ID";
	public static final String KEY_UPTB_EVENTID = "TempID";
	public static final String KEY_UPTB_MASTER = "Master";
	public static final String KEY_UPTB_CONTENT = "Content";
	public static final String KEY_UPTB_ISFINISHED = "IsFinished";
	public static final String KEY_UPTB_UPDATE_TYPE = "UpdateType";
	public static final String KEY_UPTB_TIMECREATED = "TimeCreated";
	
	// Debt Table
	public static final String KEY_DETB_ID = "ID";
	public static final String KEY_DETB_RENTER = "Renter";
	public static final String KEY_DETB_DEBTOR = "Debtor";
	public static final String KEY_DETB_MONEY = "Money";
	public static final String KEY_DETB_ISPAID = "IsPaid";
	
	
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE_EVENT_TABLE =
        "CREATE TABLE EventTable (  ID INTEGER PRIMARY KEY AUTOINCREMENT," +
        "EventID INTEGER NULL , Master INTEGER NULL ," +
        "Title VARCHAR(45) NULL , " +
        "ContactMemeber VARCHAR(45) NULL , " +
        "Location VARCHAR(45) NULL," +
        "IsTerminated TINYINT(1) NULL DEFAULT '0' )";

    private static final String DATABASE_CREATE_CONTACT_LIST_TABLE = "CREATE TABLE LocalContactListTable ( UserID INTEGER NULL ," +
    "UserName VARCHAR(45) NULL , PhoneNumber  VARCHAR(45) NULL )";
    
    private static final String DATABASE_CREATE_PRODUCT_LIST_TABLE = 
    	"CREATE TABLE ProductList_%s ( ID INTEGER PRIMARY KEY AUTOINCREMENT, ProductID INTEGER NULL ," +
    "Owner INTEGER NULL , ProductName  VARCHAR(45) NULL," +
    "Description VARCHAR(45) NULL, Price FLOAT NULL ," +
	"Quality FLOAT NULL ,Image VARCHAR(45) NULL )";
    
    private static final String DATABASE_CREATE_DEBT_TABLE = 
    	"CREATE TABLE DebtTable_%s ( ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
    	"Renter INTEGER NULL ," +
    	"Debtor INTEGER NULL , " +
    	"Money FLOAT NULL," +
    	"IsPaid TINYINT(1) NULL DEFAULT '0' )";
    
    private static final String DATABASE_CREATE_UPDATE_TABLE = 
    	"CREATE TABLE UpdateTable (" +
							"ID INTEGER PRIMARY KEY AUTOINCREMENT," +
							"TempID INTEGER NOT NULL ," +
							"Master INTEGER NULL ," +
							"Content VARCHAR(255) NULL ," +
							"IsFinished TINYINT(1) NULL DEFAULT '0' ," +
							"UpdateType INTEGER NULL ," +
							"TimeCreated DATETIME DEFAULT CURRENT_TIMESTAMP )";
    
    
    private static final String DATABASE_NAME = "com.polyu.MoneyTracker";
    private static final int DATABASE_VERSION = 2;
    
    public void DeleteDB(){
    	context.deleteDatabase(DATABASE_NAME);
    }
    
    //--------------------
    //	SQL DataBase
    //--------------------
    private SQLiteDatabase mDb;
    
    public static void CreateIntance( Context ctx )
    {
    	context = ctx;
    	if( null == mSQLManager ){
    		mSQLManager = new SQLManager( ctx );
    	}
    }
    
    
    public static SQLManager GetInstance()
    {
    	return mSQLManager;
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public SQLManager(Context ctx) {
    	super( ctx, DATABASE_NAME, null, DATABASE_VERSION);
        //this.mCtx = ctx;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	Log.e( "Money", "onCreate");
    	// 
    	// Create Contact List Table
    	db.execSQL( DATABASE_CREATE_CONTACT_LIST_TABLE );
    	//
    	// Create Event Table;
        db.execSQL( DATABASE_CREATE_EVENT_TABLE );
        
        // Create Update Table
        db.execSQL( DATABASE_CREATE_UPDATE_TABLE );
        
        //db.execSQL( DATABASE_CREATE_PRODUCT_LIST_TABLE );
        
    }
    
    @Override
    public void close() {
    	
    	
    	
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w( "Money", "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
//            db.execSQL("DROP TABLE IF EXISTS notes");
//            onCreate(db);
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public SQLManager open() throws SQLException {
        //mDbHelper = new DatabaseHelper(mCtx);
        mDb = getWritableDatabase();
        return this;
    }
    
    public void CreateProductList( String aUnqiueID )
    {
    	String query = String.format(
    			DATABASE_CREATE_PRODUCT_LIST_TABLE,
    			aUnqiueID );
    	mDb.execSQL( query );
    }
    
    public void CreateDebtTable(  String aTempEventID )
    {
    	String query = String.format(
    			DATABASE_CREATE_DEBT_TABLE,
    			aTempEventID );
    	mDb.execSQL( query );
    }
    
//    /**
//     * Create a new note using the title and body provided. If the note is
//     * successfully created return the new rowId for that note, otherwise return
//     * a -1 to indicate failure.
//     * 
//     * @param title the title of the note
//     * @param body the body of the note
//     * @return rowId or -1 if failed
//     */
    public long InsertEvent( String aEventID, String aMaster, String aTitle, String aContactMember, String aLocation ) {
    	Log.e("Money", "InsertEvent");
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_EVTTB_EVENTID, aEventID );
        initialValues.put(KEY_EVTTB_MASTER, aMaster );
        initialValues.put(KEY_EVTTB_TITLE, aTitle);
        initialValues.put(KEY_EVTTB_CONTACTMEMBER, aContactMember );
        initialValues.put(KEY_EVTTB_LOCATION, aLocation );
        return mDb.insert(TB_EVENTTABLE, null, initialValues);
    }
    
    public long InsertContactListMember( String aUserName, String aPhoneNumber )
    {
    	ContentValues initialValues = new ContentValues();
        //initialValues.put(KEY_LCLTB_USERID, aUserID);
        initialValues.put(KEY_LCLTB_USERNAME, aUserName);
        initialValues.put(KEY_LCLTB_PHONENUMBER, aPhoneNumber);
        return mDb.insert(TB_LOCAL_CONTACTLIST_TABLE, null, initialValues);
    }
    
    public long InsertUpdateTable( String aTempID , String aMaster, String aContent, int aUpdateType )
    {
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_UPTB_EVENTID, aTempID);
        initialValues.put(KEY_UPTB_MASTER, aMaster);
        initialValues.put(KEY_UPTB_CONTENT, aContent);
        initialValues.put(KEY_UPTB_ISFINISHED, false);
        initialValues.put(KEY_UPTB_UPDATE_TYPE, aUpdateType);
//        initialValues.put(KEY_UPTB_TIMECREATED, "(DATETIME('now'))" );
    	return mDb.insert(TB_UPDATETABLE, null, initialValues);
    }
    
    public long InsertProduct( String aTempEventID, String aOwner, String aProductName, String aDescription, String aPrice, String aQuality, String aImage  )
	{
    	String tableName = String.format( TB_PRODUCT_TABLE, aTempEventID );
    	ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_PLTB_OWNER, aOwner);
        initialValues.put(KEY_PLTB_PRODUCT_NAME, aProductName);
        initialValues.put(KEY_PLTB_DESCRIPTION, aDescription);
        initialValues.put(KEY_PLTB_PRICE, aPrice);
        initialValues.put(KEY_PLTB_QUALITY, aQuality);
        initialValues.put(KEY_PLTB_IMAGE, aImage);
    	return mDb.insert( tableName , null, initialValues);
	}
    
    public long InsertProductWithEventID( String aEventID, String aProductID, String aOwner, String aProductName, String aDescription, String aPrice, String aQuality, String aImage )
    {
    	Cursor eventInfo = mDb.query( TB_EVENTTABLE, 
        		null,
        		KEY_EVTTB_EVENTID + "=?",
        		new String[]{ aEventID }, null, null, null);
    	eventInfo.moveToNext();
    	
    	String tempEventID = eventInfo.getString( eventInfo.getColumnIndex( KEY_EVTTB_ID ) );
    	
    	String tableName = String.format( TB_PRODUCT_TABLE, tempEventID );
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_PLTB_PRODUCT_ID, aProductID );
        initialValues.put(KEY_PLTB_OWNER, aOwner);
        initialValues.put(KEY_PLTB_PRODUCT_NAME, aProductName);
        initialValues.put(KEY_PLTB_DESCRIPTION, aDescription);
        initialValues.put(KEY_PLTB_PRICE, aPrice);
        initialValues.put(KEY_PLTB_QUALITY, aQuality);
        initialValues.put(KEY_PLTB_IMAGE, aImage);
    	return mDb.insert( tableName , null, initialValues);
    }
    
    public long InsertDebtItem( String aTempEventID, String aRenter, String aDebtor, String aMoney )
    {
    	String debtTable = String.format( TB_DEBT_TABLE, aTempEventID );
 
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_DETB_RENTER, aRenter );
        initialValues.put(KEY_DETB_DEBTOR, aDebtor);
        initialValues.put(KEY_DETB_MONEY, aMoney);
    	return mDb.insert( debtTable , null, initialValues);
    }

	public void UpdateContactListMember( String aUserID, String aPhoneNumber )
    {
    	ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_LCLTB_USERID, aUserID);
    	mDb.update(TB_LOCAL_CONTACTLIST_TABLE, initialValues, KEY_LCLTB_PHONENUMBER + "=?", new String[] { aPhoneNumber } );
    }
    
    
    public void UpdateEventTable( String aTempEventID, String aEventID  )
    {
    	ContentValues initialValues = new ContentValues();
        initialValues.put( KEY_EVTTB_EVENTID, aEventID );
    	mDb.update( TB_EVENTTABLE, initialValues, KEY_EVTTB_ID + "=?", new String[]{ aTempEventID });
    }
    
    public void UpdateProductListTable( String aTempEventID, String aTempProductID, String aProductID )
    {
    	String productTable = String.format( TB_PRODUCT_TABLE, aTempEventID );
        
    	ContentValues initialValues = new ContentValues();
        initialValues.put( KEY_PLTB_PRODUCT_ID, aProductID );
    	mDb.update( productTable, initialValues, KEY_PLTB_ID + "=?", new String[]{ aTempProductID });
    }

//    /**
//     * Return a Cursor over the list of all notes in the database
//     * 
//     * @return Cursor over all notes
//     */
    public Cursor FetchEventInfo() {
        return mDb.query( TB_EVENTTABLE, 
        		null,
//        		new String[]{
//        		KEY_EVTTB_EVENTID, 
//        		KEY_EVTTB_MASTER,
//        		KEY_EVTTB_TITLE,
//        		KEY_EVTTB_CONTACTMEMBER,
//        		KEY_EVTTB_LOCATION }, 
        		null, null, null, null, null);
    }
    
    
    public Cursor FetchEventInfoWithID( String aTempID )
    {
    	return mDb.query( TB_EVENTTABLE, 
        		null,
        		KEY_EVTTB_ID + "=?",
        		new String[]{ aTempID }, null, null, null);
    }
    
    public Cursor FetchContactList(){
        return mDb.query( TB_LOCAL_CONTACTLIST_TABLE, 
        		null,
        		null, null, null, null, null);
    }
    
    public Cursor FetchUser(String aTempUserID){
        return mDb.query( TB_LOCAL_CONTACTLIST_TABLE, 
        		null,
        		KEY_LCLTB_USERID + "= ?", new String [] { aTempUserID }, null, null, null);
    }
    
    public Cursor FetchProductItem( String aTempEventID, String aTempProudctID ){
    	
    	String productTable = String.format( TB_PRODUCT_TABLE, aTempEventID );
        return mDb.query( productTable, 
        		null,
        		KEY_PLTB_ID + "= ?", new String [] { aTempProudctID }, null, null, null);
    }
    
    public Cursor FetchProductList( String aTempEventID ){
    	
    	String productTable = String.format( TB_PRODUCT_TABLE, aTempEventID );
        return mDb.query( productTable, 
        		null, 
        		null, null, null, null, null);
    }
    
    public Cursor FetchDebtList( String aTempEventID ){
    	
    	String debtTable = String.format( TB_DEBT_TABLE, aTempEventID );
        return mDb.query( debtTable, 
        		null, 
        		null, null, null, null, null);
    }
    
    public Cursor FetchUpdateTableWithID( Integer aID )
    {
    	return mDb.query( TB_UPDATETABLE, 
        		null,
        		KEY_UPTB_ID + "=?", new String[]{ "" + aID },
        		null, null, null );
    }
    
    public Cursor FetchUpdateTable()
    {
    	return mDb.query( TB_UPDATETABLE, 
        		null,
        		KEY_UPTB_ISFINISHED + "=?", new String[]{ "0" },
        		null, null, KEY_UPTB_TIMECREATED );
    }
    
    public Cursor FetchAllContactListWithoutUserID()
    {
    	return mDb.query( TB_LOCAL_CONTACTLIST_TABLE, 
        		null,
        		KEY_LCLTB_USERID + " IS NULL", null,
        		null, null, null );
    }
    
    public String GetMappedTempEventID( String aEventID )
    {
    	Cursor cursor = mDb.query( TB_EVENTTABLE, 
    			new String[]{ KEY_EVTTB_ID },
    			KEY_EVTTB_EVENTID + "=?", new String[]{ aEventID }, 
        		null, null, null);
    	cursor.moveToFirst();
    	String id = cursor.getString( 0 );
    	cursor.close();
		return id;
    }
    
    public String GetExistEventID( String aTempID )
	{
    	Cursor cursor = mDb.query( TB_EVENTTABLE, 
    			new String[]{ KEY_EVTTB_EVENTID },
    			KEY_EVTTB_ID + "=?", new String[]{ aTempID }, 
        		null, null, null);
    	cursor.moveToFirst();
    	String id = cursor.getString( 0 );
    	cursor.close();
		return id;
	}
    public boolean IsPhoneNumberExist( String aPhoneNum )
    {
    	System.out.println("Start IsPhoneNumberExist!");
    	Cursor cursor = mDb.query( TB_LOCAL_CONTACTLIST_TABLE, 
        		null,
        		KEY_LCLTB_PHONENUMBER + "=?", new String[]{ aPhoneNum }, 
        		null, null, null);
    	System.out.println("Cursor Success!Next go Loop");
    	
    	if( cursor.getCount() > 0 ){
    		System.out.println("Return True");
    		return true;
    	}
    	System.out.println("Return False");
    	return false;
    }
    
    public String GetLastIndex( String aTableName )
    {
    	String lastIndex = null;
        Cursor cursor = mDb.query(
                "sqlite_sequence",
                new String[]{"seq"},
                "name = ?",
                new String[]{ aTableName },
                null,
                null,
                null,
                null
        );
        if (cursor.moveToFirst()) {
        	lastIndex = cursor.getString(cursor.getColumnIndex("seq"));
        }
        cursor.close();
    	return lastIndex;
    }


	public Cursor GetContactListItemWithPhoneNum( String aPhoneNumber )
    {
    	return mDb.query( TB_LOCAL_CONTACTLIST_TABLE, 
        		null,
        		KEY_LCLTB_PHONENUMBER + "=?", new String[]{ aPhoneNumber }, null, null, null);
    }


}
